﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Globalization;
using System.Threading;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable dt_rep = new DataTable();
        private delegate void Deleg();
        private Deleg del;
        private SqlStorProc.sp_params[] rep_params = new SqlStorProc.sp_params[5];
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");

                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[0];
                DataTable tt = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_TradeTechSel", 30, par);

                if (tt.Rows.Count > 0)
                    foreach (DataRow row in tt.Rows)
                    {
                        NewItem ni = new NewItem() { ParamID = row[0], ParamComment = row[1].ToString() };
                        checkedListBox1.Items.Add(ni);
                    }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            dateTimePicker1.Value = DateTime.Now.AddDays(-1);
            dateTimePicker2.Value = DateTime.Now.AddDays(-1);
            dateTimePicker3.Value = DateTime.Now.AddDays(-1);
            dateTimePicker4.Value = DateTime.Now.AddDays(-1);
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();

            rep_params[0].name = "@datebeg";
            rep_params[0].value = dateTimePicker1.Value.Date.ToString("yyyyMMdd");
            rep_params[1].name = "@dateend";
            rep_params[1].value = dateTimePicker2.Value.Date.ToString("yyyyMMdd");
            rep_params[2].name = "@datebegprev";
            rep_params[2].value = dateTimePicker3.Value.Date.ToString("yyyyMMdd");
            rep_params[3].name = "@dateendprev";
            rep_params[3].value = dateTimePicker4.Value.Date.ToString("yyyyMMdd");
            rep_params[4].name = "@colors";
            DataTable col = new DataTable();
            col.Columns.Add("item");
            foreach (object ob in checkedListBox1.CheckedItems)
            {
                col.Rows.Add((ob as NewItem).ParamID);
            }
            rep_params[4].value = col;

            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_Stars2Rep", 300, rep_params);

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }



        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //string filename = "";
            //DialogResult dr = DialogResult.OK;
            //if (filename == "")
            //{
            //    FileDialog sfd = new SaveFileDialog();
            //    sfd.FileName = "Звезды 2";
            //    sfd.Filter = "Excel | *.xlsx";
            //    dr = sfd.ShowDialog();
            //    if (dr == DialogResult.OK) filename = sfd.FileName;
            //    sfd = null;
            //}

            //if (dr == DialogResult.OK)
            //{
                CreateFile(dt_rep, true);
            //}
        }

        private string CreateFile(DataTable days, bool open)
        {;
            string filename = "Фармацевты";
            CultureInfo oldCI = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            Hashtable myHashtable = new Hashtable();
            myHashtable = CheckExcellProcesses(myHashtable);

            try
            {
                Excel.Application exapp = new Excel.ApplicationClass();
                Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];
                exws.Name = "звезды 2";


                foreach (DataColumn col in days.Columns)
                {
                    string colName = col.ColumnName;
                    int colOrd = col.Ordinal + 1;
                    exws.get_Range(exws.Cells[1, colOrd], exws.Cells[1, colOrd]).Value2 = colName;
                }

                object[,] days_mass = ToArray(days);
                int colcount = days.Columns.Count;
                int rowcount = days.Rows.Count;
                exws.get_Range(exws.Cells[2, 1], exws.Cells[rowcount + 1, colcount]).Value2 = days_mass;
                
                for (int i = 2; i <= rowcount + 1; i++)
                {
                    int r = Convert.ToInt32(exws.get_Range(exws.Cells[i, colcount - 2], exws.Cells[i, colcount - 2]).Value2);
                    int g = Convert.ToInt32(exws.get_Range(exws.Cells[i, colcount - 1], exws.Cells[i, colcount - 1]).Value2);
                    int b = Convert.ToInt32(exws.get_Range(exws.Cells[i, colcount], exws.Cells[i, colcount]).Value2);

                    exws.get_Range(exws.Cells[i, 1], exws.Cells[i, colcount - 3]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(r, g, b));
                }



                exws.get_Range(exws.Cells[2, 6], exws.Cells[rowcount + 1, 6]).NumberFormat = "##0.00%";
                exws.get_Range(exws.Cells[2, 9], exws.Cells[rowcount + 1, 9]).NumberFormat = "##0.00%";

                exws.get_Range(exws.Cells[1, 1], exws.Cells[rowcount + 1, colcount - 3]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colcount - 3]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colcount - 3]).WrapText = true;
                exws.get_Range(exws.Cells[1, 1], exws.Cells[rowcount + 1, colcount - 3]).Cells.Font.Name = "Calibri";
                exws.get_Range(exws.Cells[1, 1], exws.Cells[rowcount + 1, colcount - 3]).Cells.Font.Size = 11;
                exws.get_Range(exws.Cells[1, 1], exws.Cells[rowcount + 1, colcount - 3]).EntireColumn.AutoFit();
                for (int i = 0; i < 3; i++)
                {
                    exws.get_Range(exws.Cells[1, colcount - i], exws.Cells[1, colcount - i]).Cells.ColumnWidth = 0;
                }

                exapp.DisplayAlerts = false;

                if (open) exapp.Visible = true;
                else
                {
                    filename = Application.StartupPath + "\\" + filename + ".xls";
                    exwb.SaveAs(filename,
                        Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null,
                        Excel.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);

                    exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                    exapp.Quit();
                }
            }
            catch (Exception)
            {
                //MessageBox.Show(ex.Message);
                filename = "";
            }
            if (!open) KillExcel(myHashtable);
            Thread.CurrentThread.CurrentCulture = oldCI;
            return filename;
        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        public static object[,] ToArray(DataTable data)
        {
            var ret = Array.CreateInstance(typeof(object), data.Rows.Count, data.Columns.Count) as object[,];
            for (int i = 0; i < data.Rows.Count; i++)
                for (int j = 0; j < data.Columns.Count; j++)
                    ret[i, j] = data.Rows[i][j];
            return ret;
        }
    }
}
